﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using MySql.Data.MySqlClient;
// access 2007 string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\\biblo.accdb";
// access 2003 string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\Biblo2.mdb"; 


namespace BDDSynth
{
    public enum SerializationFormat { DataSet };
    public partial class Form1 : Form
    {
        bool access, sql, mysql;

        // ACCESS IDENTIFICATION


        OleDbConnection cn;
        OleDbDataAdapter da;
        OleDbCommandBuilder combuilder;

        DataSet ds;


        SqlConnection cnx;
        SqlDataAdapter da2;
        SqlCommandBuilder combuilder2;

        MySqlConnection cnw;
        MySqlDataAdapter da3;
        MySqlCommandBuilder combuilder3;

        public Form1()
        {
            InitializeComponent();
        }

        private void rB_access_CheckedChanged(object sender, EventArgs e)
        {
            access = true;
            sql = false;
            mysql = false;
        }
        private void rB_sql_CheckedChanged(object sender, EventArgs e)
        {
            access = false;
            sql = true;
            mysql = false;
        }
        private void rB_mysql_CheckedChanged(object sender, EventArgs e)
        {
            mysql = true;
            access = false;
            sql = false;
        }
        //______________________________________________________________________________        

        private void b_read_Click(object sender, EventArgs e)
        {
            if (access)
            {
                bool Table;
                if ((Basename.Text == "") | (Tablename.Text == ""))
                {
                    Table = true;
                    if (Table)
                        MessageBox.Show("Probleme de Saisie de Champs", "Erreur");
                }
                else
                {
                    //Pour recompiler les données modifiées avant de les remettre dans le "DataAdapter"
                    cn = new OleDbConnection();
                    try
                    {
                        // Connexion à la base de données 

                        string connStrACCESS = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\" + Basename.Text + ".mdb";
                        cn.ConnectionString = connStrACCESS;
                        cn.Open();

                    }
                    catch
                    {
                        MessageBox.Show("erreur de connexion à la base ACCESS");
                    }
                    ds = new DataSet();
                    da = new OleDbDataAdapter();
                    string query = "SELECT * FROM " + Tablename.Text;
                    da.SelectCommand = new OleDbCommand(query, cn);
                    da.Fill(ds, Tablename.Text);
                    dataGridView1.DataSource = ds.Tables[0];
                    dataGridView1.Refresh();
                    label4.Text = " Apercu de la Table : " + Basename.Text + " " + ds.Tables[0].TableName + " Base de Données ACCESS ";
                }
            }

            if (sql)
            {
                bool Table;
                if ((Basename.Text == "") | (Tablename.Text == ""))
                {
                    Table = true;
                    if (Table)
                        MessageBox.Show("Probleme de Saisie de Champs", "Erreur");
                }
                else
                {
                    try
                    {
                        string cnxStringSQL = "Data Source =METWIN\\SQLEXPRESS;Initial Catalog =" + Basename.Text + ";Integrated Security = true;";
                        cnx = new SqlConnection(cnxStringSQL);
                        cnx.Open();
                    }
                    catch
                    {
                        MessageBox.Show("erreur de connexion à la base SQL");
                    }

                    string cmdString = "SELECT * FROM dbo." + Tablename.Text;
                    da2 = new SqlDataAdapter(cmdString, cnx);
                    ds = new DataSet("Nothing");
                    da2.Fill(ds, Tablename.Text); // Donne un nom à la table créée.
                    dataGridView1.DataSource = ds.Tables[0];
                    dataGridView1.Refresh();
                    label4.Text = " Apercu de la Table : " + Basename.Text + " " + ds.Tables[0].TableName + " Base de Données en SQL ";
                }
            }

            if (mysql)
            {
                bool Table;
                if ((Basename.Text == "") | (Tablename.Text == ""))
                {
                    Table = true;
                    if (Table)
                        MessageBox.Show("Probleme de Saisie de Champs", "Erreur");
                }
                    else
                    {
                        String connStrMysql = "server=localhost;user id=root; password=; database=" + Basename.Text + "; pooling=false";
                        cnw = new MySqlConnection(connStrMysql);
                        try
                        {
                            cnw.Open();
                        }
                        catch
                        {
                            MessageBox.Show("connexion impossible a la base de donnée MySQL");
                        }
                        string mysqlstring = "select * from " + Tablename.Text;
                        da3 = new MySqlDataAdapter(mysqlstring, cnw);
                        ds = new DataSet("Nothing");
                        da3.Fill(ds, Tablename.Text);
                        dataGridView1.DataSource = ds.Tables[0];
                        dataGridView1.Refresh();
                        label4.Text = " Apercu de la Table : " + Basename.Text + " " + ds.Tables[0].TableName + " Base de Données en MySQL ";
                    }
                
            }
        }
        //______________________________________________________________________________

        private void b_write_Click(object sender, EventArgs e)
        {
            if (access)
            {
                combuilder = new OleDbCommandBuilder(da);
                da.Update(ds, Tablename.Text); // mise a jour de la base access
                combuilder.Dispose();
            }
            if (sql)
            {
                combuilder2 = new SqlCommandBuilder(da2);
                da2.Update(ds, Tablename.Text);
                combuilder2.Dispose();
            }
            if (mysql)
            {
                combuilder3 = new MySqlCommandBuilder(da3);
                da3.Update(ds, Tablename.Text);
                combuilder3.Dispose();
            }
        }
        //______________________________________________________________________________
        private void c_access_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start(@"C:\Program Files\Microsoft Office\OFFICE11\msaccess.exe");
        }
        //______________________________________________________________________________

        private void c_sql_Click(object sender, EventArgs e)
        {
            CreateSQL SQLcreator = new CreateSQL();
            SQLcreator.ShowDialog();
            SQLcreator.Close();
        }
        //______________________________________________________________________________

        private void c_mysql_Click(object sender, EventArgs e)
        {
            CreateMySQL MySQLcreator = new CreateMySQL();
            MySQLcreator.ShowDialog();
            MySQLcreator.Close();
        }
        //______________________________________________________________________________

        private void b_convertSQL_Click(object sender, EventArgs e)
        {
            try
            {
                try
                {
                    string cnxStringSQL = "Data Source = A4-P07\\SQLEXPRESS;Initial Catalog =;Integrated Security = true;";
                    SqlConnection cnx = new SqlConnection(cnxStringSQL);
                    cnx.Open();

                    try
                    {
                        bool databas = true;
                        try
                        {
                            string sqlrequete = "DROP DATABASE " + Basename.Text;
                            SqlCommand command = new SqlCommand(sqlrequete, cnx);
                            command.ExecuteNonQuery();
                            databas = false;

                        }
                        catch { databas = false; }
                        if (!databas)
                        {
                            string sqlrequete = "CREATE DATABASE " + Basename.Text;
                            SqlCommand command2 = new SqlCommand(sqlrequete, cnx);
                            command2.ExecuteNonQuery();
                            cnx.Close();
                        }
                    }
                    catch { }
                }
                catch
                {
                    MessageBox.Show("Erreur Création Base SQL pour conversion", "Erreur");
                }
                try
                {
                    string cnxStringSQL2 = "Data Source = A4-P07\\SQLEXPRESS;Initial Catalog =" + Basename.Text + ";Integrated Security = true;";
                    SqlConnection cnx2;
                    cnx2 = new SqlConnection(cnxStringSQL2);
                    cnx2.Open();
                    try
                    {
                        bool datable = true;
                        try
                        {
                            string sqlrequete = "DROP Table dbo." + Tablename.Text;
                            SqlCommand command = new SqlCommand(sqlrequete, cnx2);
                            command.ExecuteNonQuery();
                            datable = false;
                        }
                        catch { datable = false; }
                        if (!datable)
                        {
                            string sqlrequete = "CREATE table dbo." + Tablename.Text + "(" + ds.Tables[0].Columns[0].ToString() + " text, " + ds.Tables[0].Columns[1].ToString() + " text, " + ds.Tables[0].Columns[2].ToString() + " int, " + ds.Tables[0].Columns[3].ToString() + " datetime not null)";
                            SqlCommand command2 = new SqlCommand(sqlrequete, cnx2);
                            command2.ExecuteNonQuery();
                        }
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            string line = "INSERT INTO " + ds.Tables[0].TableName + "( ";
                            for (int j = 0; j < ds.Tables[0].Columns.Count - 1; j++)
                            {

                                line += ds.Tables[0].Columns[j].ToString() + ",";

                            }

                            line += ds.Tables[0].Columns[3].ToString() + ") VALUES ( ";
                            for (int k = 0; k < ds.Tables[0].Columns.Count - 2; k++)
                            {
                                line += "'" + ds.Tables[0].Rows[i].ItemArray.GetValue(k).ToString() + "'" + ",";
                            }
                            System.DateTime date1 = System.Convert.ToDateTime(ds.Tables[0].Rows[i].ItemArray.GetValue(3).ToString());
                            int dateDepart = date1.Year;
                            int dateRetour = DateTime.Now.Year;
                            int ages = dateRetour - dateDepart;
                            line += "'" + ages.ToString() + "'" + ",";
                            line += "'" + ds.Tables[0].Rows[i].ItemArray.GetValue(3).ToString() + "'" + ")";
                            SqlCommand com = new SqlCommand(line, cnx2);
                            com.ExecuteNonQuery();
                        }
                        string cmdString = "SELECT * FROM dbo." + Tablename.Text;
                        da2 = new SqlDataAdapter(cmdString, cnx2);
                        ds = new DataSet("Nothing");
                        da2.Fill(ds, Tablename.Text); // Donne un nom à la table créée.
                        dataGridView1.DataSource = ds.Tables[0];
                        dataGridView1.Refresh();
                        label4.Text = " Apercu de la Table : " + Basename.Text + " " + ds.Tables[0].TableName + " convertie en SQL ";
                        cnx2.Close();
                    }
                    catch { }
                }

                catch
                {
                    MessageBox.Show("Erreur Création Table SQL pour conversion", "Erreur");
                }
            }
            catch
            {
                MessageBox.Show("Erreur Conversion", "Erreur");
            }
        }


        //______________________________________________________________________________

        private void b_convertMySQL_Click(object sender, EventArgs e)
        {
            try
            {
                try
                {
                    string cnxStringSQL = "server=localhost;user id=root; password=; database=''; pooling=false";
                    MySqlConnection cnw = new MySqlConnection(cnxStringSQL);
                    cnw.Open();
                    try
                    {
                        bool databas = true;
                        try
                        {
                            string mysqlrequete = "DROP DATABASE " + Basename.Text;
                            MySqlCommand command = new MySqlCommand(mysqlrequete, cnw);
                            command.ExecuteNonQuery();
                            databas = false;

                        }
                        catch { databas = false; }
                        if (!databas)
                        {
                            string mysqlrequete = "CREATE DATABASE " + Basename.Text;
                            MySqlCommand command = new MySqlCommand(mysqlrequete, cnw);
                            command.ExecuteNonQuery();
                            cnw.Close();
                        }
                    }
                    catch { }
                }
                catch
                {
                    MessageBox.Show("Erreur Création Base MySQL pour conversion", "Erreur");
                }
                try
                {
                    string cnxStringSQL2 = "server=localhost;user id=root; password=; database=" + Basename.Text + "; pooling=false";
                    MySqlConnection cnw2;
                    cnw2 = new MySqlConnection(cnxStringSQL2);
                    cnw2.Open();
                    try
                    {
                        bool datable = true;
                        try
                        {
                            string sqlrequete = "DROP Table " + Tablename.Text;
                            MySqlCommand command = new MySqlCommand(sqlrequete, cnw2);
                            command.ExecuteNonQuery();
                            datable = false;

                        }
                        catch { datable = false; }
                        if (!datable)
                        {
                            string tables = "CREATE TABLE " + ds.Tables[0].TableName + "(" + ds.Tables[0].Columns[0].ToString() + " text, " + ds.Tables[0].Columns[1].ToString() + " text, " + ds.Tables[0].Columns[2].ToString() + " int, " + ds.Tables[0].Columns[3].ToString() + " DATETIME not null);";
                            MySqlCommand commande = new MySqlCommand(tables, cnw2);
                            commande.ExecuteNonQuery();
                            

                        }
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            string line = "INSERT INTO " + ds.Tables[0].TableName + "( ";
                            for (int j = 0; j < ds.Tables[0].Columns.Count - 1; j++)
                            {

                                line += ds.Tables[0].Columns[j].ToString() + ",";

                            }
                            line += ds.Tables[0].Columns[3].ToString() + ") VALUES ( ";
                            for (int k = 0; k < ds.Tables[0].Columns.Count - 2; k++)
                            {
                                line += "'" + ds.Tables[0].Rows[i].ItemArray.GetValue(k).ToString() + "'" + ",";
                            }
                            System.DateTime date1 = System.Convert.ToDateTime(ds.Tables[0].Rows[i].ItemArray.GetValue(3).ToString());
                            int datenaiss = date1.Year;
                            int dates = DateTime.Now.Year;
                            int ages = dates - datenaiss;
                            line += "'" + ages.ToString() + ",";
                            line += "'" + ds.Tables[0].Rows[i].ItemArray.GetValue(3).ToString()  + "'" + " )";
                            MySqlCommand com = new MySqlCommand(line, cnw2);
                            com.ExecuteNonQuery(); 
                            }
                        string cmdString = "SELECT * FROM " + Tablename.Text;
                        da3 = new MySqlDataAdapter(cmdString, cnw2);
                        ds = new DataSet("Nothing");
                        da3.Fill(ds, Tablename.Text); // Donne un nom à la table créée.
                        combuilder3 = new MySqlCommandBuilder(da3);
                        da3.Update(ds, Tablename.Text);
                        dataGridView1.DataSource = ds.Tables[0];
                        dataGridView1.Refresh();
                        cnw2.Close();
                        label4.Text = " Apercu de la Table : " + Basename.Text + " " + ds.Tables[0].TableName + " Convertie en MySQL ";
                        }
                    catch { }
                    
                }
                catch
                {
                    MessageBox.Show("Erreur Création Table MySQL pour convertion", "Erreur");
                }
            }
            catch
            {
                MessageBox.Show("Erreur Conversion MySQL", "Erreur");
            }
        }
    }
}